package com.zunipe.accounts.database.dao;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.zunipe.accounts.database.AccountDBHelper;
import com.zunipe.accounts.database.entity.Account;

import java.util.ArrayList;
import java.util.List;

public class AccountDao {
    private final AccountDBHelper dbHelper;

    public AccountDao(Context context) {
        dbHelper = new AccountDBHelper(context);
    }

    public double sumExpensesAmountByDate(String strDate, int userId) {
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        try (Cursor cursor = database.rawQuery("SELECT SUM(amount) FROM accounts WHERE strftime('%Y-%m', date) = ? AND amount < 0 AND user_id = ?",
                new String[]{strDate, String.valueOf(userId)})) {
            if (cursor.moveToNext()) {
                return cursor.getDouble(0);
            }
        }
        return 0;
    }

    public double sumIncomeAmountByDate(String strDate, int userId) {
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        try (Cursor cursor = database.rawQuery("SELECT SUM(amount) FROM accounts WHERE strftime('%Y-%m', date) = ? AND amount > 0 AND user_id = ?",
                new String[]{strDate, String.valueOf(userId)})) {
            if (cursor.moveToNext()) {
                return cursor.getDouble(0);

            }
        }
        return 0;
    }

    public Account findById(int id) {
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        try (Cursor cursor = database.rawQuery("SELECT * FROM accounts WHERE id = ?",
                new String[]{String.valueOf(id)})) {
            if (cursor.moveToNext()) {
                //int id = cursor.getInt(0);
                int userId = cursor.getInt(1);
                double amount = cursor.getDouble(2);
                String date = cursor.getString(3);
                String type = cursor.getString(4);
                String note = cursor.getString(5);
                return new Account(id, userId, amount, date, type, note);
            }
        }
        return null;
    }

    public List<Account> findByUserId(int userId) {
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        List<Account> list = new ArrayList<>();
        try (Cursor cursor = database.rawQuery("SELECT * FROM accounts WHERE user_id = ?",
                new String[]{String.valueOf(userId)})) {
            while (cursor.moveToNext()) {
                int id = cursor.getInt(0);
                //int userId = cursor.getInt(1);
                double amount = cursor.getDouble(2);
                String date = cursor.getString(3);
                String type = cursor.getString(4);
                String note = cursor.getString(5);
                list.add(new Account(id, userId, amount, date, type, note));
            }
        }
        return list;
    }

    public List<Account> findByDate(String strDate, int userId) {
        SQLiteDatabase database = dbHelper.getReadableDatabase();
        List<Account> list = new ArrayList<>();
        try (Cursor cursor = database.rawQuery("SELECT * FROM accounts WHERE strftime('%Y-%m', date) = ? AND user_id = ? ORDER BY date",
                new String[]{strDate, String.valueOf(userId)})) {
            while (cursor.moveToNext()) {
                int id = cursor.getInt(0);
                //int userId = cursor.getInt(1);
                double amount = cursor.getDouble(2);
                String date = cursor.getString(3);
                String type = cursor.getString(4);
                String note = cursor.getString(5);
                list.add(new Account(id, userId, amount, date, type, note));
            }
        }
        return list;
    }

    public int insert(Account account) {
        SQLiteDatabase database = dbHelper.getWritableDatabase();
        database.execSQL("INSERT INTO accounts(user_id, amount, date, type, notes) VALUES(?, ?, ?, ?, ?)",
                new Object[]{account.getUserId(), account.getAmount(), account.getDate(), account.getType(), account.getNotes()});
        try (Cursor cursor = database.rawQuery("SELECT last_insert_rowid()", new String[]{})) {
            if (cursor.moveToNext()) {
                int result = cursor.getInt(0);
                return result;
            }
        }
        return -1;
    }

    public void removeById(int id) {
        SQLiteDatabase database = dbHelper.getWritableDatabase();
        database.execSQL("DELETE FROM accounts WHERE id = ?", new Object[]{id});
    }

    public void removeByUserId(int userId) {
        SQLiteDatabase database = dbHelper.getWritableDatabase();
        database.execSQL("DELETE FROM accounts WHERE user_id = ?", new Object[]{userId});
    }

    public void update(Account account) {
        SQLiteDatabase database = dbHelper.getWritableDatabase();
        database.execSQL("UPDATE accounts SET amount = ?, date = ?, type = ?, notes = ? WHERE id = ?",
                new Object[]{account.getAmount(), account.getDate(), account.getType(), account.getNotes(), account.getId()});
    }

    public void updateUserId(int oldUserId, int newUserId) {
        SQLiteDatabase database = dbHelper.getWritableDatabase();
        database.execSQL("UPDATE accounts SET user_id = ? WHERE user_id = ?",
                new Object[]{newUserId, oldUserId});
    }
}
